/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.childcare.dao;

import com.aptech.childcare.until.ConfigDatabase;
import com.aptech.childcare.entities.ClassChild;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;

/**
 *
 * @author BaoTien
 */
public class ClassDAO {

    private String lastError;
    Connection con = null;
    Vector vtHeader = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ConfigDatabase db = null;
    private final String sqlCREATE = "INSERT INTO tbClass VALUES(?,?,?)";
    private final String sqlREAD = "SELECT * FROM tbClass";
    private final String sqlDELETE = "DELETE FROM tbClass WHERE ClassID = ?";
    private final String sqlUPDATE = "SELECT * FROM tbClass WHERE ClassID = ?";
    private final String sqlREADIDAGE = "SELECT * FROM tbClass WHERE AgeGroupID = ?";

    public ClassDAO() {
        db = new ConfigDatabase();
    }

    /**
     *
     * @param cl the Class to add
     * @return true if add successful, false if add fail
     */
    public boolean create(ClassChild cl) throws IOException {
        if (!exist(cl)) {
            return false;
        }
        try {
            con = db.getConnection();
            pst = con.prepareStatement(sqlCREATE);
            pst.setInt(1, cl.getAgeGroupID());
            pst.setString(2, cl.getName());
            pst.setString(3, cl.getNote());

            if (pst.executeUpdate() > 0) {
                setLastError("Add successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError("SQL error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Add fail!");
        return false;
    }

    public boolean exist(ClassChild cl) throws IOException {
        ArrayList<ClassChild> list = readByAll();
        for (ClassChild cla : list) {
            if (cl.getName().equals(cla.getName())) {
                setLastError("Class existed !");
                return false;
            }
        }
        return true;
    }

    /**
     * 
     * @param cl the Class to edit
     * @return true if edit successful, false if edit fail
     */
    public boolean update(ClassChild cl) throws IOException {
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, cl.getClassID());
            rs = pst.executeQuery();
            rs.first();
            rs.updateInt(2, cl.getAgeGroupID());
            rs.updateString(3, cl.getName());
            rs.updateString(4, cl.getNote());
            rs.updateRow();
            setLastError("Update successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Update Fail!");
        return false;
    }

    /**
     *
     * @param cl the Class to delete
     * @return true if delete successful, false if delete fail.
     */
    public boolean delete(int ClassID) throws IOException {
        try {
            con = db.getConnection();
            pst = con.prepareStatement(sqlDELETE);
            pst.setInt(1, ClassID);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete successfully!");
            }
            return true;
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        setLastError("Delete fail!");
        return false;
    }

    /**
     * 
     * @return list of Class in database
     */
    public ArrayList<ClassChild> readByAll() throws IOException {
        ArrayList<ClassChild> list = new ArrayList<ClassChild>();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlREAD);
            rs = pst.executeQuery();
            while (rs.next()) {
                ClassChild cl = new ClassChild();
                cl.setClassID(rs.getInt(1));
                cl.setAgeGroupID(rs.getInt(2));
                cl.setName(rs.getString(3));
                cl.setNote(rs.getString(4));
                list.add(cl);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return list;
    }

    /**
     * 
     * @param ClassID
     * @return
     */
    public ClassChild readByID(int ClassID) throws IOException {
        ClassChild cl = new ClassChild();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlUPDATE);
            pst.setInt(1, ClassID);
            rs = pst.executeQuery();
            while (rs.next()) {
                cl.setClassID(rs.getInt(1));
                cl.setAgeGroupID(rs.getInt(2));
                cl.setName(rs.getString(3));
                cl.setNote(rs.getString(4));
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return cl;
    }

    /**
     * 
     * @param AgegroupID
     * @return
     */
    public ClassChild readByIDAge(int AgegroupID) throws IOException {
        ClassChild cl = new ClassChild();
        con = db.getConnection();
        try {
            pst = con.prepareStatement(sqlREADIDAGE);
            pst.setInt(1, AgegroupID);
            rs = pst.executeQuery();
            while (rs.next()) {
                cl.setClassID(rs.getInt(1));
                cl.setAgeGroupID(rs.getInt(2));
                cl.setName(rs.getString(3));
                cl.setNote(rs.getString(4));
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            db.closeConnection(db);
        }
        return cl;
    }

    /**
     *
     * @param name the Class needed search
     * @return list of class
     */
    public ArrayList<ClassChild> readByName(String name) throws IOException {
        ArrayList<ClassChild> list = readByAll();
        ArrayList<ClassChild> clList = new ArrayList<ClassChild>();
        for (ClassChild cl : list) {
            if (cl.getName().toLowerCase().matches(".*" + name.trim().toLowerCase() + ".*")) {
                clList.add(cl);
            }
        }
        return clList;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
